package be.nepherte.movmanager.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import com.google.gwt.user.server.rpc.RemoteServiceServlet;
import be.nepherte.movmanager.client.DatabaseService;
import be.nepherte.movmanager.client.Movie;
import be.nepherte.movmanager.client.exceptions.InvalidLengthException;
import be.nepherte.movmanager.client.exceptions.InvalidRatingException;
import be.nepherte.movmanager.client.exceptions.InvalidTitleException;
import be.nepherte.movmanager.client.exceptions.InvalidDateException;

/**
 * This class represents a MySQL implementation of the database service. This
 * implementation corresponds with DatabaseService and DatabaseServiceAsync.
 * <p>
 * This file is part of MovManager.
 * </p>
 * <p>
 * MovManager is free software: you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or (at your option) any later
 * version.
 * </p>
 * <p>
 * MovManager is distributed in the hope that it will be useful, but WITHOUT ANY
 * WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 * A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 * </p>
 * <p>
 * You should have received a copy of the GNU General Public License along with
 * MovManager. If not, see <a
 * href="http://www.gnu.org/licenses/">http://www.gnu.org/licenses</a>.
 * </p>
 * 
 * @author Bart Verhoeven
 * @since 23/05/2009
 * @version 0.1
 */
public class MySQLDatabaseServiceImpl extends RemoteServiceServlet implements DatabaseService {
	private static final long serialVersionUID = 1L;
	/**The MySQL database instance.*/
	private static MySQLDatabaseServiceImpl db;
	/**The connection with the MySQL database.*/
	private static Connection conn;

	/**
	 * Get an instance of MySQLDatabase. If no instance exists yet, create a new one. Otherwise return the existing one.
	 * 
	 * @return	a MySQL database instance
	 */
	public static MySQLDatabaseServiceImpl getInstance()  {
		if (db != null)
			return db;

		return createInstance();
	}

	/**
	 * Create a new instance of MySQLDatabase.
	 * 
	 * @return	a new MySQL database
	 */
	private static MySQLDatabaseServiceImpl createInstance() {
		return db = new MySQLDatabaseServiceImpl();
	}

	/**
	 * Constructor for MySQLDatabase. It will create a connection with the MySQL database. Use <code>getInstance()</code> to create and retrieve
	 * an instance of MySQLDatabaseServiceImpl.
	 */
	public MySQLDatabaseServiceImpl() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			connect();
		} catch (Exception e) {
			System.out.println(e.getMessage());
		}
	}
	
	/**
	 * Connect to the database.
	 * 
	 * @throws	SQLException
	 * 			a database error occurred
	 */
	private void connect() throws SQLException {
		conn =  DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/movmanager", "user", "password");
	}
	
	/**
	 * Execute a query on the database.
	 * 
	 * @param	query
	 * 			the query you want to execute
	 * @return	the result set of the query
	 * @throws	SQLException
	 * 			a database error occurred
	 */
	private ResultSet doQuery(String query) throws SQLException {
		return conn.createStatement().executeQuery(query);
	}

	/**
	 * Get a list with all the movies available in the database.
	 * 
	 * @return	an array list with all the movies available in the database, empty when no movies are found
	 */
	public ArrayList<Movie> getMovies() {
		String query = "SELECT * FROM `Movie`;";
		return getMoviesByQuery(query);
	}
	
	/**
	 * Get a list with all genres available in the database.
	 */
	public ArrayList<String> getGenres() {
		String query = "SELECT DISTINCT `GenreName` FROM `HasGenre`;";
		return getGenresByQuery(query);
	}
	
	/**
	 * Get the genres of a given movie.
	 * 
	 * @param	movieTitle
	 * 			the movie title you want the genres of
	 * @return	an array list with all the genres of this movie, an empty list if there are no genres for this movie
	 */
	private ArrayList<String> getGenresOfMovie(String movieTitle) {
		String query = "SELECT `GenreName` FROM `HasGenre` WHERE `MovieName` = '" + movieTitle + "';";
		return getGenresByQuery(query);
	}
	
	/**
	 * Get the directors of a given movie.
	 * 
	 * @param	movieTitle
	 * 			the movie title you want the directors of
	 * @return	an array list with all the directors of this movie, an empty list if there are no directors for this movie
	 */
	private ArrayList<String> getDirectorsOfMovie(String movieTitle) {
		String query = "SELECT `DirectorName` FROM `Directed` WHERE `MovieName` = '" + movieTitle + "';";
		return getDirectorsByQuery(query);
	}
	
	/**
	 * Get the writers of this movie.
	 * 
	 * @param	movieTitle
	 * 			the movie title you want the writers of
	 * @return	an array list with all the writers of this movie, empty list if there are no writers for this movie
	 */
	private ArrayList<String> getWritersOfMovie(String movieTitle) {
		String query = "SELECT `WriterName` FROM `Written` WHERE MovieName = '" + movieTitle + "';";
		return getWritersByQuery(query);
	}
	
	/**
	 * Get the actors and their corresponding roles in a given movie.
	 * 
	 * @param	movieTitle
	 * 			the movie title you want to actors and roles from
	 * @return	A list with the actors and roles in the movie
	 */
	private LinkedHashMap<String,ArrayList<String>> getActorsAndRolesOfMovie(String movieTitle) {
		String query = "SELECT * FROM `Actors` WHERE MovieName = '" + movieTitle + "' ORDER BY `DisplayOrder` ASC;";
		return getActorsAndRolesByQuery(query);
	}
	
	/**
	 * Get movies from the database by means of a query. Note that an empty array list is possible when no genres are found. Never null.
	 * 
	 * @param	query
	 * 			the query to retrieve movies. should be of the form SELECT <columns> FROM `Movie` WHERE <attribute> = '<values>'
	 * @return	an array list with all the queried movies. empty if no movies are found
	 */
	private ArrayList<Movie> getMoviesByQuery(String query) {
		ArrayList<Movie> movies = new ArrayList<Movie>();

		try {
			ResultSet rs = doQuery(query);

			while (rs.next()) {
				Movie movie = null;
				String title = rs.getString("Title");
				try {
					movie = new Movie(title, getDirectorsOfMovie(title), getWritersOfMovie(title), rs.getDate("ReleaseDate"), rs.getInt("Duration"), rs.getDouble("Rating"), getGenresOfMovie(title), rs.getString("Summary"), getActorsAndRolesOfMovie(title), rs.getString("Studio"), rs.getInt("Watched") == 1 ? true : false, rs.getString("Image"));
				} catch (InvalidTitleException e) {
					// This should never happen. We make sure everything put in the database is valid information
					e.printStackTrace();
				} catch (InvalidDateException e) {
					// This should never happen. We make sure everything put in the database is valid information
					e.printStackTrace();
				} catch (InvalidLengthException e) {
					// This should never happen. We make sure everything put in the database is valid information
					e.printStackTrace();
				} catch (InvalidRatingException e) {
					// This should never happen. We make sure everything put in the database is valid information
					e.printStackTrace();
				} 
				movies.add(movie);
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		
		return movies;
	}
	
	/**
	 * Get a list of genres by means of a query. Note that an empty array list is possible when no genres are found. Never null.
	 * 
	 * @param	query
	 * 			the query to retrieve genres. should be of the form SELECT <columns> FROM `HasGenre` WHERE `MovieName` = '<value>';
	 * @return	an array list with all the queried genres. empty if no genres are found
	 */
	private ArrayList<String> getGenresByQuery(String query) {
		ArrayList<String> genres = new ArrayList<String>();
		
		try {
			ResultSet rs = doQuery(query);			
			while (rs.next()) {
				String genre = rs.getString("GenreName");
				genres.add(genre);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		
		return genres;
	}
	
	/**
	 * Get a list of directors by means of a query. Note that an empty array list is possible when no genres are found. Never null.
	 * 
	 * @param	query
	 * 			the query to retrieve directors. should be of the form SELECT <columns> FROM `Directed` WHERE `MovieName` = '<value>';
	 * @return	an array list with all the queried directors. empty if no directors are found
	 */
	private ArrayList<String> getDirectorsByQuery(String query) {
		ArrayList<String> directors = new ArrayList<String>();
		
		try{
			ResultSet rs = doQuery(query);
			
			while (rs.next()) {
				directors.add(rs.getString("DirectorName"));
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		
		return directors;
	}
	
	/**
	 * Get a list of writers by means of a query. Note that an empty array list is possible when no genres are found. Never null.
	 * 
	 * @param	query
	 * 			the query to retrieve writers. should be of the form SELECT <columns> FROM `Written` WHERE `MovieName` = '<value>';
	 * @return	an array list with all the queried writers. empty if no writers are found
	 */
	private ArrayList<String> getWritersByQuery(String query) {
		ArrayList<String> writers = new ArrayList<String>();
		
		try {
			ResultSet rs = doQuery(query);
			
			while (rs.next()) {
				writers.add(rs.getString("WriterName"));
			}
		} catch (SQLException e) {
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		
		return writers;
	}
	
	/**
	 * Get a map of the actors and their corresponding roles by means of a query. Note that an empty array list is possible when no genres are found. Never null.
	 * 
	 * @param	query
	 * 			the query to retrieve the actors and their roles. should be of the form SELECT <columns> FROM `Actors` WHERE <attribute> = '<value>';
	 * @return
	 */
	private LinkedHashMap<String, ArrayList<String>> getActorsAndRolesByQuery(String query) {
		LinkedHashMap<String,ArrayList<String>> actorsAndRoles = new LinkedHashMap<String,ArrayList<String>>();
		
		try {
			ResultSet rs = doQuery(query);			
			while (rs.next()) {
				String actor = rs.getString("Name");
				String role = rs.getString("Role");
				if (actorsAndRoles.containsKey(actor)) {
					ArrayList<String> roles = actorsAndRoles.get(actor);
					roles.add(role);
				}
				else {
					ArrayList<String> roles = new ArrayList<String>();
					roles.add(role);
					actorsAndRoles.put(actor, roles);
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("SQLException: " + e.getMessage());
			System.out.println("SQLState: " + e.getSQLState());
			System.out.println("VendorError: " + e.getErrorCode());
		}
		
		return actorsAndRoles;
	}
	
//	/**
//	 * A debug main method to test some queries.
//	 */
//	public static void main(String[] args) throws SQLException {
//		LinkedHashMap<String,String[]> sequence = new LinkedHashMap<String,String[]>();
//		sequence.put("Jennifer Garner", new String[] {"Jenna Rink"});
//		sequence.put("Mark Ruffalo", new String[] {"Matt Flamhaff"});
//		sequence.put("Judy Greer", new String[] {"Lucy Wyman"});
//		sequence.put("Andy Serkis", new String[]{"Richard Kneeland"});
//		
//		for (String actor: sequence.keySet()) {
//			System.out.println(actor);
//		}
//		
//		MySQLDatabaseServiceImpl db = MySQLDatabaseServiceImpl.getInstance();
//		ArrayList<Movie> movies = db.getMoviesByQuery("Select * FROM `Movie` WHERE `Title` = '88 Minutes';");
//		
//		System.out.println("Number of movies in db: " + movies.size());
//		
//		for (Movie movie: movies) {
//			System.out.println("#: " + movies.indexOf(movie));
//			System.out.println("Title: " + movie.getTitle());
//			System.out.println("Directors: " + movie.directorsToString());
//			System.out.println("Writers:" + movie.writersToString());
//			System.out.println("Duration: " + (movie.getLength() != 0 ? Integer.toString(movie.getLength()) : ""));
//			System.out.println("Rating: " + (movie.getRating() != 0 ? Double.toString(movie.getRating()) : ""));
//			System.out.println("Genres: " + movie.genresToString());
//			System.out.println("Release Date: " + (movie.getReleaseDate() != null ? movie.getReleaseDate().toLocaleString() : ""));
//			System.out.println("Watched: " + (movie.hasWatched() == true ? "yes"  : "no"));
//			System.out.print("Actors:");
//			ArrayList<String> roles = movie.getRoles("Onbestaande");
//			boolean rolesEmpty = roles.isEmpty();
//			for (String actor: movie.getActors())
//				System.out.print(" " + actor + ",");
//			System.out.println();
////			System.out.print("Roles:");
////			for (ArrayList<String> roles: movie.getRoles())
////				for (String role: roles)
////					System.out.print(" " + role + ",");
//			System.out.println();
//		}
//	}
}
